//Documentation complete
/**
 * @author Jason
 * @version 2009FE16
 */

package broker;

import problemDomain.*;
import problemDomain.EmployeeAccountBean.Position;
import static problemDomain.ErrorBean.*;
import static problemDomain.ErrorBean.ErrorCode.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import static java.sql.Types.*;

/**
 * <code>Broker</code> for <code>EmployeeAccountBean</code>.
 */
public class EmployeeAccountBroker extends Broker<EmployeeAccountBean> {
	
	/* Singleton Constructor */
	
	private static EmployeeAccountBroker broker;
	
	/**
	 * Default constructor. Calls the openConnection method.
	 * @throws ErrorBean
	 */
	private EmployeeAccountBroker() throws ErrorBean {}
	
	/**
	 * Retrieves the static EmployeeAccountBroker instance.
	 * @return static EmployeeAccountBroker
	 * @throws ErrorBean
	 */
	public synchronized static EmployeeAccountBroker getBroker () throws ErrorBean {
		if (broker == null)
			broker = new EmployeeAccountBroker();
		return broker;
	}
	
	/* END Singleton Constructor */
	
	
	/* Prepared Statements */
	
	private CallableStatement insertSQL;
	private CallableStatement getInsertSQL () throws ErrorBean {
		CallableStatement statement = prepare(insertSQL, "call sp_create_employee(?,?,?,?,?,?,?,?);");
		registerOutParameter(statement, 8, BIGINT);
		return statement;
	}
	
	private CallableStatement updateSQL;
	private CallableStatement getUpdateSQL () throws ErrorBean 
	{
		CallableStatement statement = prepare(updateSQL, "call sp_update_employee(?,?,?,?,?,?,?,?,?);");
		registerOutParameter(statement, 9, BIGINT);
		return statement;
	}
	
	private PreparedStatement deleteEmployeeSQL;
	private PreparedStatement getDeleteEmployeeSQL () throws ErrorBean {
		return prepare(deleteEmployeeSQL, "UPDATE employee SET active = FALSE WHERE employee_id = ?;");
	}
	
	private CallableStatement validatePasscodeSQL;
	private CallableStatement getValidatePasscodeSQL () throws ErrorBean {
		//passcode, id, username, firstname, lastname, position
		CallableStatement statement = prepare(validatePasscodeSQL, "CALL sp_validate_employee_passcode(?, ?, ?, ?, ?, ?);");
		registerOutParameter(statement, 2, BIGINT);
		registerOutParameter(statement, 3, VARCHAR);
		registerOutParameter(statement, 4, VARCHAR);
		registerOutParameter(statement, 5, VARCHAR);
		registerOutParameter(statement, 6, VARCHAR);
		return statement;
	}
	
	private CallableStatement validateUsernameSQL;
	private CallableStatement getValidateUsernameSQL () throws ErrorBean {
		CallableStatement statement = prepare(validateUsernameSQL, "CALL sp_validate_employee_username(?, ?, ?, ?, ?, ?);");
		registerOutParameter(statement, 3, BIGINT);
		registerOutParameter(statement, 4, VARCHAR);
		registerOutParameter(statement, 5, VARCHAR);
		registerOutParameter(statement, 6, VARCHAR);
		return statement;
	}
	
	/* END Prepared Statements */
	
	
	/* (Broker) Methods */
	
	@Override
	public synchronized boolean persist(EmployeeAccountBean eab) throws ErrorBean {			
		eab.isValid();
		
		int employee_id = eab.getID();		
		String first_name = eab.getFirstname();
		String last_name = eab.getLastname();
		String username = eab.getUsername();
		String password = eab.getPassword();
		Position position = eab.getPosition();
		long pass_code = eab.getPasscode();
		
		if (employee_id == 0) {
			try {
				CallableStatement insertSQL = getInsertSQL();
				insertSQL.setString(1, position.toString());
				insertSQL.setString(2, first_name);
				insertSQL.setString(3, last_name);
				insertSQL.setString(4, username);
				insertSQL.setString(5, password);
				insertSQL.setLong(6, pass_code);
				insertSQL.setInt(7, 1);
				insertSQL.execute();
				
				return insertSQL.getInt(8)>0;
			} catch(SQLException e) {
				throwError(
					BROKER_INSERT_ERROR,
					e.getMessage(),
					this);
			}
		}
		else if (employee_id > 0) {
			try {
				CallableStatement updateSQL = getUpdateSQL();
				updateSQL.setInt(1, employee_id);
				updateSQL.setString(2, position.toString());
				updateSQL.setString(3, first_name);
				updateSQL.setString(4, last_name);
				updateSQL.setString(5, "");
				updateSQL.setString(6, "");
				updateSQL.setLong(7, 0);
				updateSQL.setInt(8, 1);
				updateSQL.execute();
				if(updateSQL.getInt(9)!=1)
					throwError(
						BROKER_UPDATE_ERROR,
						this);
				
				return true;
			} catch(SQLException e) {
				throwError(
					BROKER_UPDATE_ERROR,
					e.getMessage(),
					this);
			}
		}		
		return false;
	}

	@Override
	public synchronized boolean remove(EmployeeAccountBean eab) throws ErrorBean {
		int employee_id = eab.getID();
		
		try {
			PreparedStatement deleteEmployeeSQL = getDeleteEmployeeSQL();
			deleteEmployeeSQL.setInt(1, employee_id);
			deleteEmployeeSQL.execute();			
			return true;
		} catch(SQLException e) {
			throwError(
				BROKER_DELETE_ERROR,
				e.getMessage(),
				this);
			return false;
		}
	}
	
	@Override
	public synchronized List<EmployeeAccountBean> search(EmployeeAccountBean searchBean) throws ErrorBean {
		List<EmployeeAccountBean> search = new ArrayList<EmployeeAccountBean>();
		EmployeeAccountBean eab;
		
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM employee WHERE active = TRUE");
		
		//Search by Employee ID
		int id = searchBean.getID();
		if (id > 0) {
			sql.append(" AND employee_id = " + id);
		}
		else {
			//Search by Employee position
			Position position = searchBean.getPosition();
			if(position!=null) {
				sql.append(" AND position LIKE '%" + position.toString() + "%'");
			}
			
			//Search by Employee first name
			String first_name = searchBean.getFirstname();
			if (!"".equals(first_name)) {
				sql.append(" AND first_name LIKE '%" + first_name + "%'");
			}
			
			//Search by Employee last name
			String last_name = searchBean.getLastname();
			if (!"".equals(last_name)) {
				sql.append(" AND last_name LIKE '%" + last_name + "%'");
			}
			
			//Search by Employee username
			String username = searchBean.getUsername();
			if (!"".equals(username)) {
				sql.append(" AND username = '" + username + "'");
			}
		}
		
		sql.append(';');
		
		try	{
			Statement stmt = conn.createStatement();
			if (!sql.toString().equals("")) {
				ResultSet results = stmt.executeQuery(sql.toString());
				
				while (results.next()) {
					eab = new EmployeeAccountBean();
					eab.setID(results.getInt("employee_id"));
					eab.setFirstname(results.getString("first_name"));
					eab.setLastname(results.getString("last_name"));
					eab.setUsername(results.getString("username"));
					eab.setPosition(Position.translate(results.getString("position")));
					
					search.add(eab);
				}
			}
		} catch (SQLException e) {
			throwError(
				BROKER_SELECT_ERROR,
				e.getMessage(),
				this);
		}
		return search;
	}
	
	@Override
	public synchronized List<EmployeeAccountBean> listAll() throws ErrorBean {
		List<EmployeeAccountBean> search = new ArrayList<EmployeeAccountBean>();
		EmployeeAccountBean eab;

		StringBuilder sql = new StringBuilder("SELECT * FROM employee WHERE active=1;");
		
		try	{
			Statement stmt = conn.createStatement();
			if (!sql.toString().equals("")) {
				ResultSet results = stmt.executeQuery(sql.toString());
				
				while (results.next()) {
					eab = new EmployeeAccountBean();
					eab.setID(results.getInt("employee_id"));
					eab.setFirstname(results.getString("first_name"));
					eab.setLastname(results.getString("last_name"));
					eab.setUsername(results.getString("username"));
					eab.setPosition(Position.translate(results.getString("position")));
					search.add(eab);
				}
			}
		} catch (SQLException e) {
			throwError(
				BROKER_SELECT_ERROR,
				e.getMessage(),
				this);
		}
		return search;
	}
	
	/* END (Broker) Methods */
	
	
	/* Methods */
	
	/**
	 * Validates the login information of an employee account against the database.
	 * @param bean the account information to validate
	 * @return the relevant <code>EmployeeAccountBean</code> of the account information is valid
	 * @throws ErrorBean if the account information is not valid
	 */
	public synchronized EmployeeAccountBean validate (EmployeeAccountBean bean) throws ErrorBean {
		
		long passcode = bean.getPasscode();
		
		//Validate by passcode
		if (passcode > 0) {
			try {
				CallableStatement validatePasscodeSQL = getValidatePasscodeSQL();
				validatePasscodeSQL.setLong(1, passcode);
				validatePasscodeSQL.execute();
				
				int id = validatePasscodeSQL.getInt(2);
				if (id == 0)
					throwError(
						EMPLOYEE_PASS_CODE_INVALID,
						this);
				
				//valid
				EmployeeAccountBean result = new EmployeeAccountBean();
				result.setID(0);
				result.setUsername(validatePasscodeSQL.getString(3));
				result.setFirstname(validatePasscodeSQL.getString(4));
				result.setLastname(validatePasscodeSQL.getString(5));
				result.setPosition(Position.translate(validatePasscodeSQL.getString(6)));
				return result;
			} catch (SQLException e) {
				throwError(
					BROKER_SELECT_ERROR,
					e.getMessage(),
					this);
				return null;
			}
		}
		
		String username = bean.getUsername();
		String password = bean.getPassword();
		
		//Validate by username/password
		try {
			CallableStatement validateUsernameSQL = getValidateUsernameSQL();
			validateUsernameSQL.setString(1, username);
			validateUsernameSQL.setString(2, password);
			validateUsernameSQL.execute();
			
			int id = validateUsernameSQL.getInt(3);
			if (id == 0)
				throwError(
					EMPLOYEE_INCORRECT_USERNAME_OR_PASSWORD,
					this);
			
			//valid
			EmployeeAccountBean result = new EmployeeAccountBean();
			result.setID(id);
			result.setUsername(username);
			result.setFirstname(validateUsernameSQL.getString(4));
			result.setLastname(validateUsernameSQL.getString(5));
			result.setPosition(Position.translate(validateUsernameSQL.getString(6)));
			return result;
		} catch (SQLException e) {
			throwError(
				BROKER_SELECT_ERROR,
				e.getMessage(),
				this);
			return null;
		}
		
	}
	
	/* END Methods */
	
}
